package cn.hd01.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import cn.hd01.repository.entity.RebateBook;

@Repository
public interface RebateBookRepository extends JpaRepository<RebateBook, Integer> {
	Page<RebateBook> findAll(Specification<RebateBook> spec, Pageable pageable);
	
	@Query(value="SELECT COUNT(1) FROM (SELECT c.id, c.name, SUM(IF(p.is_balance = 0, p.distributor_rebate, 0)), SUM(IF(p.is_balance = 1, p.distributor_rebate, 0)) FROM customer c LEFT JOIN c_order o ON c.id = o.distributor_id LEFT JOIN c_order_payment p ON o.id = p.order_id WHERE c.type = 1 AND c.recommend_distributor_id = ?1 GROUP BY c.id,  c.name, DATE_FORMAT(p.create_time,'%Y-%m')) temp", nativeQuery=true)
	Integer countAgent(Integer recommendDisId);
	//SELECT b.ref_id, b.name, SUM(rebate), DATE_FORMAT(b.create_time,'%Y-%m') FROM customer c  LEFT JOIN rebate_book b ON c.id = b.ref_id WHERE c.type = 1  AND c.recommend_distributor_id = 28 AND b.type = 0 GROUP BY b.ref_id, b.name, DATE_FORMAT(b.create_time,'%Y-%m') order by DATE_FORMAT(b.create_time,'%Y-%m') desc
	
	@Query(value="SELECT c.id, c.name, DATE_FORMAT(p.create_time,'%Y-%m'), SUM(IF(p.is_balance = 0, p.recomm_distributor_rebate, 0)), SUM(IF(p.is_balance = 1, p.recomm_distributor_rebate, 0)) FROM customer c LEFT JOIN c_order o ON c.id = o.distributor_id INNER JOIN c_order_payment p ON o.id = p.order_id WHERE c.type = 1 AND c.recommend_distributor_id = ?1 GROUP BY c.id,  c.name, DATE_FORMAT(p.create_time,'%Y-%m') order by DATE_FORMAT(p.create_time,'%Y-%m') desc limit ?2, ?3", nativeQuery=true)
	List<Object> findAgent(Integer recommendDisId, Integer page, Integer pageSize);
}
